CREATE TABLE stu2
(
student_id nchar(10),
name nvarchar(20) Not Null, --無論如何這個欄位的資料不能為空
old INT,
class nvarchar(5),
PRIMARY KEY (student_id) --設定學號為主鍵
);
CREATE TABLE book2
(
book_id nchar(10),
book_name nvarchar(50),
borrower nchar(10),
PRIMARY KEY (book_id), --設定書籍編號為主鍵
FOREIGN KEY (borrower) REFERENCES stu2(student_id) --設定外來鍵,參考stu2這張表
);
SELECT * FROM stu2;
SELECT * FROM book2;
INSERT INTO stu2 (student_id, name, old, class) VALUES
('s1001', N'老王', 23, N'資工四1'),
('s1002', N'老陳', 21, N'資工二1'),
('s1003', N'老林', 22, N'資工三1');
INSERT INTO stu2 (student_id, name, old) VALUES ('s1004', N'老白', 18);
SELECT student_id from stu2;
SELECT student_id, name, class
FROM stu2
WHERE student_id = 's1001'; --只搜s1001學號的學生
UPDATE stu2
SET class=N'老服一1'
WHERE student_id='s1004';
INSERT into book2(book_id,book_name, borrower) VALUES
('b1001', N'哈利波特', 's1001'),
('b1002', N'暮光之城', 's1001'),
('b1003', N'從零開始', 's1002');
DELETE FROM stu2 WHERE student_id='s1002'; --不能直接刪
DELETE from book2 where borrower = 's1002'; --必須先刪這個後才能
AND OR
LIKE 稍微篩選。
order by desc/asc 排序。
join inner/left/righ/full 可以自己動手嘗試。
參考資料、更多內容:https://ithelp.ithome.com.tw/articles/10215741 https://hackmd.io/@kycz0620/SJFg_lman https://www.fooish.com/sql/